﻿namespace ClassLibrary.BusinessLayer.BusinessObjects
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Reflection;
    using System.Text;

    public abstract class ParentTableClass
    {
        private DbCommand cmd;
        private ClassPropertyAttribute csAttribute;
        private PropertyInfo[] piClassProperties;

        protected ParentTableClass()
        {
        }

        public DbCommand CreateDeleteCommandParameters()
        {
            this.cmd = Db.GetStoredProcCommand(this.GetTableName() + "Delete_SP");
            this.piClassProperties = base.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in this.piClassProperties)
            {
                this.csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if ((this.csAttribute != null) && this.csAttribute.IsDelete)
                {
                    DbParameter param = this.cmd.CreateParameter();
                    param.ParameterName = this.csAttribute.ParamName.ToString();
                    param.DbType = this.csAttribute.FieldType;
                    param.Value = piClassProperty.GetValue(this, null);
                    this.cmd.Parameters.Add(param);
                }
            }
            return this.cmd;
        }

        public DbCommand CreateGetCommandParameters()
        {
            this.cmd = Db.GetStoredProcCommand(this.GetTableName() + "Get_SP");
            this.piClassProperties = base.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in this.piClassProperties)
            {
                this.csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if ((this.csAttribute != null) && this.csAttribute.IsSelect)
                {
                    DbParameter param = this.cmd.CreateParameter();
                    param.ParameterName = this.csAttribute.ParamName.ToString();
                    param.DbType = this.csAttribute.FieldType;
                    param.Value = piClassProperty.GetValue(this, null);
                    this.cmd.Parameters.Add(param);
                }
            }
            return this.cmd;
        }

        public DbCommand CreateGetsCommandParameters()
        {
            this.cmd = Db.GetStoredProcCommand(this.GetTableName() + "Gets_SP");
            return this.cmd;
        }

        public DbCommand CreateInsertCommandParameters()
        {
            this.cmd = Db.GetStoredProcCommand(this.GetTableName() + "Insert_SP");
            Type tClassType = base.GetType();
            ClassTableAttribute tbAttribute = (ClassTableAttribute) Attribute.GetCustomAttribute(tClassType, typeof(ClassTableAttribute));
            this.piClassProperties = tClassType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in this.piClassProperties)
            {
                this.csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if ((this.csAttribute != null) && this.csAttribute.IsInsert)
                {
                    DbParameter param = this.cmd.CreateParameter();
                    param.ParameterName = this.csAttribute.ParamName.ToString();
                    param.DbType = this.csAttribute.FieldType;
                    param.Value = piClassProperty.GetValue(this, null);
                    this.cmd.Parameters.Add(param);
                }
            }
            return this.cmd;
        }

        public DbCommand CreateListCommandParameters()
        {
            this.cmd = Db.GetStoredProcCommand(this.GetTableName() + "List_SP");
            this.piClassProperties = base.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in this.piClassProperties)
            {
                this.csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if ((this.csAttribute != null) && this.csAttribute.IsChild)
                {
                    DbParameter param = this.cmd.CreateParameter();
                    param.ParameterName = this.csAttribute.ParamName.ToString();
                    param.DbType = this.csAttribute.FieldType;
                    param.Value = piClassProperty.GetValue(this, null);
                    this.cmd.Parameters.Add(param);
                }
            }
            return this.cmd;
        }

        public DbCommand CreateUpdateCommandParameters()
        {
            this.cmd = Db.GetStoredProcCommand(this.GetTableName() + "Update_SP");
            this.piClassProperties = base.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in this.piClassProperties)
            {
                this.csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if ((this.csAttribute != null) && this.csAttribute.IsUpdate)
                {
                    DbParameter param = this.cmd.CreateParameter();
                    param.ParameterName = this.csAttribute.ParamName.ToString();
                    param.DbType = this.csAttribute.FieldType;
                    param.Value = piClassProperty.GetValue(this, null);
                    this.cmd.Parameters.Add(param);
                }
            }
            return this.cmd;
        }

        public string GenerateSQLException(SqlException ex)
        {
            StringBuilder errorMessages = new StringBuilder();
            for (int i = 0; i < ex.Errors.Count; i++)
            {
                errorMessages.Append(string.Concat(new object[] { "Index #", i, "<br>\nMessage: <b><font color='red'>", ex.Errors[i].Message, "</b></font><br>\nLineNumber: ", ex.Errors[i].LineNumber, "<br>\nSource: ", ex.Errors[i].Source, "</br>\nProcedure: ", ex.Errors[i].Procedure, "</br>\n" }));
            }
            return errorMessages.ToString();
        }

        private bool GetIdentity()
        {
            ClassTableAttribute tbAttribute = (ClassTableAttribute) Attribute.GetCustomAttribute(base.GetType(), typeof(ClassTableAttribute));
            return ((tbAttribute != null) && tbAttribute.ReturnIdentity);
        }

        public DbCommand GetsDataReader(string SPName, DbParameter[] param)
        {
            this.cmd = Db.GetStoredProcCommand(SPName);
            foreach (DbParameter db in param)
            {
                this.cmd.Parameters.Add(db);
            }
            return this.cmd;
        }

        private string GetTableName()
        {
            ClassTableAttribute tbAttribute = (ClassTableAttribute) Attribute.GetCustomAttribute(base.GetType(), typeof(ClassTableAttribute));
            if (tbAttribute != null)
            {
                return tbAttribute.TableName;
            }
            return "";
        }

        public void LoadDataToCollection(DataRow dataRow)
        {
            PropertyInfo[] piClassProperties = base.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in piClassProperties)
            {
                ClassPropertyAttribute csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if (csAttribute != null)
                {
                    try
                    {
                        piClassProperty.SetValue(this, dataRow[csAttribute.ColumnName], null);
                    }
                    catch
                    {
                    }
                }
            }
        }

        public void LoadDataToCollection(IDataReader idr)
        {
            PropertyInfo[] piClassProperties = base.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in piClassProperties)
            {
                ClassPropertyAttribute csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if (csAttribute != null)
                {
                    try
                    {
                        piClassProperty.SetValue(this, idr[csAttribute.ColumnName], null);
                    }
                    catch
                    {
                    }
                }
            }
        }

        public void LoadDataToCollection(IDataReader idr, PropertyInfo piChildProperty)
        {
            PropertyInfo[] piChildClassProperties = piChildProperty.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo piClassProperty in this.piClassProperties)
            {
                ClassPropertyAttribute csAttribute = (ClassPropertyAttribute) Attribute.GetCustomAttribute(piClassProperty, typeof(ClassPropertyAttribute));
                if (!((csAttribute == null) || csAttribute.IsChild))
                {
                    piClassProperty.SetValue(this, idr[csAttribute.ColumnName], null);
                }
                if ((csAttribute != null) && csAttribute.IsChild)
                {
                    this.LoadDataToCollection(idr, piClassProperty);
                }
            }
        }

        public bool Save(string transactionType)
        {
            bool ret;
            if (transactionType == "I")
            {
                this.cmd = this.CreateInsertCommandParameters();
            }
            else if (transactionType == "D")
            {
                this.cmd = this.CreateDeleteCommandParameters();
            }
            else
            {
                this.cmd = this.CreateUpdateCommandParameters();
            }
            try
            {
                Db.ExecuteNonQuery(this.cmd);
                ret = true;
            }
            catch (SqlException e)
            {
                throw new Exception(this.GenerateSQLException(e));
            }
            finally
            {
                this.cmd.Dispose();
            }
            return ret;
        }

        public int Save(string transactionType, bool ReturnIdentity)
        {
            int ret;
            if (transactionType == "I")
            {
                this.cmd = this.CreateInsertCommandParameters();
                DbParameter param = this.cmd.CreateParameter();
                param.ParameterName = "RETURN_VALUE";
                param.Direction = ParameterDirection.ReturnValue;
                this.cmd.Parameters.Add(param);
            }
            else if (transactionType == "D")
            {
                this.cmd = this.CreateDeleteCommandParameters();
            }
            else
            {
                this.cmd = this.CreateUpdateCommandParameters();
            }
            try
            {
                Db.ExecuteNonQuery(this.cmd);
                if ((this.GetIdentity() && (transactionType == "I")) && ReturnIdentity)
                {
                    return (int) this.cmd.Parameters["RETURN_VALUE"].Value;
                }
                ret = 1;
            }
            catch (SqlException e)
            {
                throw new Exception(this.GenerateSQLException(e));
            }
            finally
            {
                this.cmd.Dispose();
            }
            return ret;
        }

        public string Save(string transactionType, string OutputName)
        {
            string ret;
            if (transactionType == "I")
            {
                this.cmd = this.CreateInsertCommandParameters();
                DbParameter param = this.cmd.CreateParameter();
                param.ParameterName = OutputName;
                param.Direction = ParameterDirection.Output;
                param.Size = 50;
                this.cmd.Parameters.Add(param);
            }
            else if (transactionType == "D")
            {
                this.cmd = this.CreateDeleteCommandParameters();
            }
            else
            {
                this.cmd = this.CreateUpdateCommandParameters();
            }
            try
            {
                Db.ExecuteNonQuery(this.cmd);
                if (transactionType == "I")
                {
                    return (string) this.cmd.Parameters[OutputName].Value;
                }
                ret = null;
            }
            catch (SqlException e)
            {
                throw new Exception(this.GenerateSQLException(e));
            }
            catch (Exception x)
            {
                throw new Exception(x.Message);
            }
            finally
            {
                this.cmd.Dispose();
            }
            return ret;
        }

        public DbCommand UpdateQuery(string SPName, DbParameter[] param)
        {
            this.cmd = Db.GetStoredProcCommand(SPName);
            foreach (DbParameter db in param)
            {
                this.cmd.Parameters.Add(db);
            }
            return this.cmd;
        }
    }
}

